/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package repositorio;

//import Basicas.Dados;
import Basicas.MegaSena;
import dao.DAO;
import excecoes.ConcursoInexistenteException;
import excecoes.DAOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * 
 * @author Graduando
 */
public class RepositorioMegaSenaBD extends DAO implements RepositorioMegaSena {
		
    @Override
	public void inserirJogo(String[][] mega, int size) {
        Connection con = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        con = getConnection(false);
        
                try {
                   for(int i = 1; i < size; i++) { 
                    
                    pstmt = con.prepareStatement("INSERT INTO JOGOS (concurso, dataSorteio, primeiraDezena, "
                    + "segundaDezena, terceiraDezena, quartaDezena, quintaDezena, sextaDezena, arrecadacaoTotal, "
                    + "ganhadoresSena, rateioSena, ganhadoresQuina, rateioQuina, ganhadoresQuadra, rateioQuadra, "
                    + "acumulado, valorAcumulado, estimativaPremio, acumuladoMegaDaVirada) VALUES "
                  // + "(1, '11/03/1996', 04,05,30,33,41,52,0.00,0,0.00,17,39158.92,2016,330.21,'SIM',1714650.23,0.00,0.00)", Statement.RETURN_GENERATED_KEYS);
                            + "( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)", Statement.RETURN_GENERATED_KEYS);

                    pstmt.setInt(1, Integer.parseInt(mega[i][0]));
                    System.out.println(mega[i][0]);
                    pstmt.setString(2, mega[i][1]);
                    pstmt.setInt(3, Integer.parseInt(mega[i][2]));
                    pstmt.setInt(4, Integer.parseInt(mega[i][3]));
                    pstmt.setInt(5, Integer.parseInt(mega[i][4]));
                    pstmt.setInt(6, Integer.parseInt(mega[i][5]));
                    pstmt.setInt(7, Integer.parseInt(mega[i][6]));
                    pstmt.setInt(8, Integer.parseInt(mega[i][7]));
                    String valor = mega[i][8].replaceAll("[. ,]", "");
                    pstmt.setDouble(9, Double.parseDouble(valor));
                    pstmt.setInt(10, Integer.parseInt(mega[i][9]));
                    valor = mega[i][10].replaceAll("[. ,]", "");
                    pstmt.setDouble(11, Double.parseDouble(valor));
                    pstmt.setInt(12, Integer.parseInt(mega[i][11]));
                    valor = mega[i][12].replaceAll("[. ,]", "");
                    pstmt.setDouble(13, Double.parseDouble(valor));
                    pstmt.setInt(14, Integer.parseInt(mega[i][13]));
                    valor = mega[i][14].replaceAll("[. ,]", "");
                    pstmt.setDouble(15, Double.parseDouble(valor));
                    pstmt.setString(16, mega[i][15]);
                    valor = mega[i][16].replaceAll("[. ,]", "");
                    pstmt.setDouble(17, Double.parseDouble(valor));
                    valor = mega[i][17].replaceAll("[. ,]", "");
                    pstmt.setDouble(18, Double.parseDouble(valor));
                    valor = mega[i][18].replaceAll("[. ,]", "");
                    pstmt.setDouble(19, Double.parseDouble(valor));
                pstmt.executeUpdate();
               rs = pstmt.getGeneratedKeys();
                   }   
               
               con.commit();
                 System.out.println("Adicinou!!!");
                } catch (SQLException ex) {
                    try {
                        con.rollback();
                    } catch (SQLException ex1) {
                        throw new DAOException(ex1);
                    }

                    throw new DAOException(ex);
               
                } finally {
                    try {
                        if (rs != null) {
                            rs.close();
                        }

                        if (pstmt != null) {
                            pstmt.close();
                        }

                        if (con != null) {
                            con.close();
                        }
                    } catch (SQLException ex) {
                        throw new DAOException(ex);
                    }
                }
            }
    public void deleteDados(){
        Connection con = null;
        PreparedStatement pstmt = null;
        
        try{
            con = getConnection(false);
            String remove = "truncate TABLE jogos";
            pstmt = con.prepareStatement(remove);
            pstmt.executeUpdate();
            con.commit();
        }catch(Exception e){
            e.printStackTrace();
        }finally{
          try{  
            if(pstmt != null){
                   pstmt.close();
            }
            
            if(con != null){
                con.close();
            }
          }catch(SQLException e){
              e.printStackTrace();
          }
        }
       
    }
    public int procuraNumero(int valor){
        Connection con = null;
        Statement pstmt = null;
        ResultSet rs = null;
        int resultado = 0;
        
        try{
        con = getConnection(false);
        String select = "SELECT COUNT(*) FROM JOGOS WHERE "
                + "primeiraDezena = "+valor+" or segundaDezena = "+valor+" or terceiraDezena = "+valor+""
                + "or quartaDezena = "+valor+" or quintaDezena = "+valor+" or sextaDezena = "+valor;
        pstmt = con.createStatement();
        rs = pstmt.executeQuery(select);
        if(rs.next()){
            resultado = rs.getInt("COUNT");
        }
        }catch(SQLException e){
            e.printStackTrace();
        }finally{
            try {
                if(rs != null){
                    rs.close();
                }
                
                if(pstmt != null){
                    pstmt.close();
                }
                
                if(con != null){
                    con.close();
                }
                
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return resultado;
        
    }
}
